﻿using System;
using System.Collections.Generic;

using System.Text;
using HZ.Data;
using System.Data;

namespace HZ.DrugTrade.DAL
{
    public class DAL_ReturnMerchandise
    {
        /// <summary>
        /// 批量插入退货单明细
        /// </summary>
        /// <param name="returnMerchandise"></param>
        /// <param name="detaillist"></param>
        /// <returns></returns>
        public static int AddReturnMerchandiseDetail_Batch(HZ.DrugTrade.Model.ReturnMerchandise returnMerchandise, IList<HZ.DrugTrade.Model.ReturnMerchandiseDetail> detaillist)
        {
            int Flag = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();
                foreach (var item in detaillist)
                {
                    item.ReturnSerialID = returnMerchandise.ReturnSerialID;
                    h.ExecuteNonQuery(HZ.Data.DAL_Helper.GetInsertSql(item));
                    //h.ExecuteNonQuery(string.Format("Update TB_PurchaseOrderDetail set returncount={1} where orderdetailid='{0}'", item.OrderDetailID, item.ReturnCount));
                }
               
                h.ExecuteNonQuery(string.Format(@"UPDATE TB_ReturnMerchandise SET ReturnOrderStatus = 1,ReturnAmount=	(SELECT
		                                                                    SUM (ReturnCount*RetrunPrice)
	                                                                       FROM
		                                                                    TB_ReturnMerchandiseDetail
	                                                                    WHERE ReturnSerialID='{0}') WHERE ReturnSerialID = '{1}'", returnMerchandise.ReturnSerialID, returnMerchandise.ReturnSerialID));


                h.CommitTrans();
                Flag = 1;
            }
            catch (Exception ee)
            {
                HZ.Common.log4netManager.LogDebug("DAL_ReturnMerchandise", "批量插入退货单明细", ee);
                h.RollTrans();
                Flag = 0;
            }
            return Flag;

        }
        /// <summary>
        /// 判断退货产品是否在退货流程中 add by zdc
        /// </summary>
        /// <param name="procurecatalogID"></param>
        /// <param name="price"></param>
        /// <param name="orgID"></param>
        /// <returns></returns>
        public static bool IsReturnning(string procurecatalogID, decimal? price,string orgID)
        {
            bool flag = false;
            string sqlInfo = string.Format("select * from TB_returnmerchandisedetail where OrderDetailID='{0}' and ReturnState=0", procurecatalogID);
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sqlInfo);
            if (dt.Rows.Count>0)
            {
                flag = true;
            }
            return flag;
        }
        public static bool IsInvoiced(string OrderDetailID)
        {
            bool flag = false;
            string sqlInfo = string.Format("select * from Tb_Distribution where OrderDetailID='{0}' and isinvoice=1", OrderDetailID);
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sqlInfo);
            if (dt.Rows.Count > 0)
            {
                flag = true;
            }
            return flag;
        }
        public static IList<HZ.DrugTrade.Model.View.ReturnMerchandise> GetReturnReturnMerchandis_Company(HZ.DrugTrade.Model.Query.ReturnMerchandise where, string companyid, int currentpage, int pagesize, out int totalrecord)
        {
            string strwhere = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition(where, true);

            string sql = string.Format(@"SELECT DISTINCT A.AreaID,A.ReturnTime,A.ReturnName,A.ReturnAmount,A.ReturnSerialID,A.HospitalID,A.HospitalName FROM View_ReturnMerchandise A INNER JOIN  TB_ReturnMerchandiseDetail B ON A.ReturnSerialID = B.ReturnSerialID WHERE CompanyID = '{0}' AND B.ReturnState = 0 {1}", companyid, strwhere);

            PageDescribe des = new PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;

            return HZ.Data.DAL_Helper.CommonFillList<Model.View.ReturnMerchandise>(des.GetSpecailPage());
        }

        /// <summary>
        /// 获取需要退货的产品
        /// </summary>
        /// <param name="where"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static IList<HZ.DrugTrade.Model.View.ReturnMerchandiseHospital> GetReturnMerchandis_Hospital(HZ.DrugTrade.Model.Query.ReturnMerchandiseHospital where, int currentpage, int pagesize, out int totalrecord)
        {
            //            where.Append = string.Format(@"ProcureCatalogID NOT IN (SELECT A.ProcureCatalogID FROM 
            //TB_ReturnMerchandiseDetail A INNER JOIN TB_ReturnMerchandise N ON A.ReturnSerialID = N.ReturnSerialID
            //WHERE N.HospitalID = '{0}' AND A.ReturnState != 1)", where.HospitalID);

            //string sql = string.Format("SELECT * FROM View_ReturnMerchandiseHospital WHERE 1=1 and Stock>0 {0}", HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition(where,true));
            string sql = string.Format(@"select * from (SELECT c.batchrecordid,c.isinvoice,nvl(b.HASRETURNCOUNT,0) HASRETURNCOUNT,(a.warehousecount - nvl(b.HASRETURNCOUNT,0)) ResidualReturncount,a.*
  FROM View_PurchaseOrderDetail A LEFT JOIN (SELECT orderdetailid,RETURNCOUNT HASRETURNCOUNT FROM TB_RETURNMERCHANDISEDETAIL WHERE RETURNSTATE = 1) B
  ON A.orderdetailid = b.orderdetailid left join tb_distribution c on A.orderdetailid = c.orderdetailid )
  WHERE 1=1 {0}", HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition(where, true));

            PageDescribe des = new PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;


            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.ReturnMerchandiseHospital>(des.GetSpecailPage());

        }

        public static int SubmitReturnMerchandise(HZ.DrugTrade.Model.ReturnMerchandise ReturnMerchandise)
        {
            return HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(HZ.Data.DAL_Helper.GetUpdateSql(ReturnMerchandise));
        }

        public static int SubmitReturnMerchandise_Batch_Hospital(IList<HZ.DrugTrade.Model.ReturnMerchandise> list)
        {
            int Flag = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();
                int s = 0;

                foreach (var item in list)
                {
                    DataTable dt= h.ExecuteTable(string.Format("select * from Tb_Returnmerchandisedetail a inner join Tb_Distribution b on a.orderdetailid=b.orderdetailid where b.isinvoice=1 and a.returnserialid='{0}'", item.ReturnSerialID));
                    if (dt.Rows.Count > 0)
                    {
                        s = 1;
                        break;
                    }
                        //UPDATE TB_ReturnMerchandise SET ReturnOrderStatus='2' WHERE 1=1 AND ReturnSerialID='5feea5e6-2a10-40e9-aa07-28dbb2611c72'
                    h.ExecuteNonQuery(string.Format("UPDATE TB_ReturnMerchandise SET ReturnOrderStatus='2' WHERE ReturnSerialID='{0}'", item.ReturnSerialID));
                
                }
                if (s > 0)
                {
                    h.RollTrans();
                    Flag = 2;
                }

                else
                {
                    h.CommitTrans();
                    Flag = 1;
                }
            }
            catch (Exception ee)
            {
                HZ.Common.log4netManager.LogDebug("DAL_ReturnMerchandise", "SubmitReturnMerchandise_Batch_Hospital", ee);
                h.RollTrans();
                Flag = 0;
            }
            return Flag;

        }

        public static int DeleteReturnMerchandise_Batch_Hospital(IList<HZ.DrugTrade.Model.ReturnMerchandise> list)
        {
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();
                foreach (var item in list)
                {
                    int count = int.Parse((h.ExecuteTable(string.Format("SELECT COUNT(*) FROM TB_ReturnMerchandise WHERE returnorderstatus = 2 AND ReturnSerialID = '{0}'", item.ReturnSerialID))).Rows[0][0].ToString());
                    if (count > 0)
                    {
                        return 2;
                    }
                    string sql = string.Format("update  Tb_Purchaseorderdetail set returncount=0 where OrderDetailID in (select procurecatalogid from TB_returnmerchandisedetail  where ReturnSerialID='{0}')", item.ReturnSerialID);
                    string deInfo = string.Format("delete TB_ReturnMerchandiseDetail where ReturnSerialID='{0}'",item.ReturnSerialID);
                    h.ExecuteNonQuery(deInfo);
                    h.ExecuteNonQuery(sql);
                   // h.ExecuteNonQuery(HZ.Data.DAL_Helper.GetDeleteSql(item));
                    //string straa=HZ.Data.DAL_Helper.GetDeleteSql(item);
                    HZ.Data.DAL_Helper.OperateTableSQL<HZ.DrugTrade.Model.ReturnMerchandise>(item,HZ.Data.TableOperator.Delete,h);
                }


                h.CommitTrans();
            }
            catch (Exception ee)
            {
                HZ.Common.log4netManager.LogDebug("DAL_ReturnMerchandise", "DeleteReturnMerchandise_Batch_Hospital", ee);
                h.RollTrans();
                throw ee;
                return 0;
            }
            return 1;

        }

        public static int DeleteReturnMerchandiseDetail_Batch_Hospital(IList<HZ.DrugTrade.Model.ReturnMerchandiseDetail> list)
        {
            if (list.Count == 0)
            {
                return 1;
            }
            int Flag = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();
                StringBuilder sb = new StringBuilder();
                foreach (var item in list)
                {
                    string strsql = string.Format(@"select returnorderstatus from tb_returnmerchandise where returnserialid in (select returnserialid from TB_ReturnMerchandiseDetail WHERE   ReturnSerialDetailID = '{0}')", item.ReturnSerialDetailID);
                    if (Convert.ToInt32(h.ExecuteScalar(strsql)) == 2)
                    {
                        return 2;
                    }
                }

                sb.Append(" begin ");
                foreach (var item in list)
                {
                    // h.ExecuteNonQuery(HZ.Data.DAL_Helper.GetDeleteSql(item));
                    sb.Append(string.Format("DELETE FROM TB_ReturnMerchandiseDetail WHERE   ReturnSerialDetailID = '{0}' ;", item.ReturnSerialDetailID));
                    sb.Append(string.Format("update  Tb_Purchaseorderdetail set returncount=0 where OrderDetailID in (select procurecatalogid from TB_returnmerchandisedetail  where ReturnSerialDetailID='{0}') ;", item.ReturnSerialDetailID));
                }
                sb.Append(" end; ");
                h.ExecuteNonQuery(sb.ToString());
                h.CommitTrans();
                Flag = 1;
            }
            catch (Exception ee)
            {
                HZ.Common.log4netManager.LogDebug("DAL_ReturnMerchandise", "DeleteReturnMerchandiseDetail_Batch_Hospital", ee);
                h.RollTrans();
                Flag = 0;
            }
            return Flag;

        }

        public static int UpdateReturnMerchandiseDetail_Batch_Hospital(IList<HZ.DrugTrade.Model.ReturnMerchandiseDetail> list)
        {
            if (list==null||list.Count==0)
            {
                return 1;
            }
            int Flag = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();
                StringBuilder sb = new StringBuilder();
                foreach (var item in list)
                {
                    string strsql = string.Format(@"select returnorderstatus from tb_returnmerchandise where returnserialid in (select returnserialid from TB_ReturnMerchandiseDetail WHERE   ReturnSerialDetailID = '{0}')", item.ReturnSerialDetailID);
                    if (Convert.ToInt32(h.ExecuteScalar(strsql)) == 2)
                    {
                        return 2;
                    }
                    string strsqls = string.Format(@"select count(*) from TB_ReturnMerchandiseDetail WHERE   ReturnSerialDetailID = '{0}'", item.ReturnSerialDetailID);
                    if (Convert.ToInt32(h.ExecuteScalar(strsqls)) == 0)
                    {
                        return 3;
                    }
                }
                sb.Append(" begin ");
                foreach (var item in list)
                {
                    sb.Append(string.Format("UPDATE TB_ReturnMerchandiseDetail SET ReturnSerialID='{0}',CompanyID='{1}',ReturnCount={2},RetrunPrice={3} WHERE  ReturnSerialDetailID='{4}' ;", item.ReturnSerialID, item.CompanyID, item.ReturnCount, item.RetrunPrice, item.ReturnSerialDetailID));
                    //h.ExecuteNonQuery(HZ.Data.DAL_Helper.GetUpdateSql(item));
                }


                /*BUG:不应当将ReturnOrderStatus更新成3，    这样就将数据彻底搞乱*/
                //string ReturnSerialID = list[0].ReturnSerialID;
                //int count = int.Parse(h.ExecuteScalar(string.Format("SELECT COUNT(*) FROM TB_ReturnMerchandiseDetail WHERE ReturnSerialID = '{0}' AND  ReturnState != 0", ReturnSerialID)).ToString());
                //if (count == 0)
                //{
                //    //h.ExecuteNonQuery(string.Format("UPDATE TB_ReturnMerchandise SET ReturnOrderStatus = 3 WHERE ReturnSerialID = '{0}'", ReturnSerialID));
                //    sb.Append(string.Format("UPDATE TB_ReturnMerchandise SET ReturnOrderStatus = 3 WHERE ReturnSerialID = '{0}' ; ", ReturnSerialID));
                //}

                sb.Append(" end; ");
                h.ExecuteNonQuery(sb.ToString());
                h.CommitTrans();
                Flag = 1;
            }
            catch (Exception ee)
            {
                HZ.Common.log4netManager.LogDebug("DAL_ReturnMerchandise", "UpdateReturnMerchandiseDetail_Batch_Hospital", ee);
                h.RollTrans();
                Flag = 0;
            }
            return Flag;

        }

        public static IList<HZ.DrugTrade.Model.View.ReturnMerchandise> GetTreatedReturnMerchandis_Company(HZ.DrugTrade.Model.Query.ReturnMerchandise where, string companyid, int currentpage, int pagesize, out int totalrecord)
        {
            string strwhere = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition(where, true);

            string sql = string.Format(@"SELECT DISTINCT A.AreaID,A.ReturnTime,A.ReturnName,A.ReturnAmount,A.ReturnSerialID,A.HospitalID,A.HospitalName FROM View_ReturnMerchandise A INNER JOIN  TB_ReturnMerchandiseDetail B ON A.ReturnSerialID = B.ReturnSerialID WHERE CompanyID = '{0}' AND B.ReturnState != 0 {1}", companyid, strwhere);

            PageDescribe des = new PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;

            return HZ.Data.DAL_Helper.CommonFillList<Model.View.ReturnMerchandise>(des.GetSpecailPage());
        }
    }
}
